package code14JDBC;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 黄衡熙
 * @version 1.0
 */

public class Test08_多表查询操作 {
    public static void main(String[] args) {
        List<Emp> list = selectAll();
        for (Emp emp1 : list) {
            System.out.println(emp1);
        }
        Dept dept = selectById(1);
        System.out.println(dept);
    }

    /**
     * 多对一查询
     * 查询所有员工以及其所属部门信息
     *
     * @return
     */
    public static List<Emp> selectAll() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Emp> list = new ArrayList<>();
        try {
            conn = JDBCUtil.getConnection();
            String sql = new StringBuilder()
                    .append("select e.id, e.name, e.salary, e.dept_id, d.id 'd.id', d.name 'd.name', d.remark 'd.remark' ")
                    .append(" from t_emp e ")
                    .append(" left join t_dept d ")
                    .append(" on e.dept_id = d.id")
                    .toString();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Emp emp = new Emp();
                emp.setId(rs.getInt("id"));
                emp.setName(rs.getString("name"));
                emp.setSalary(rs.getDouble("salary"));

                Dept dept = new Dept();
                dept.setId(rs.getInt("d.id"));
                dept.setName(rs.getString("d.name"));
                dept.setRemark(rs.getString("d.remark"));
                emp.setDept(dept);
                list.add(emp);
            }
            return list;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtil.close(conn, ps, rs);
        }

    }

    /**
     * 一对多查询
     * 根据部门编号查询部门以及该部门中的所有员工信息
     *
     * @param id
     * @return
     */
    public static Dept selectById(Integer id) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Dept dept = null;

        try{
            conn = JDBCUtil.getConnection();
            String sql=new StringBuffer()
                    .append("select d.id,d.name,d.remark,e.id 'e.id',e.name 'e.name',e.salary,e.dept_id ")
                    .append("from t_dept d left join t_emp e ")
                    .append(" on d.id=e.dept_id")
                    .append(" where d.id=?")
                    .toString();
            ps=conn.prepareStatement(sql);
            ps.setInt(1, id);
            rs=ps.executeQuery();
            while (rs.next()) {
                if(dept==null) {
                    dept = new Dept();
                    dept.setId(rs.getInt("id"));
                    dept.setName(rs.getString("name"));
                    dept.setRemark(rs.getString("remark"));
                }
                Emp emp = new Emp();
                emp.setId(rs.getInt("id"));
                emp.setName(rs.getString("name"));
                emp.setSalary(rs.getDouble("salary"));
                dept.addEmp(emp);
            }
            return dept;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtil.close(conn, ps, rs);
        }

    }
}
